IF EXISTS ( SELECT 'X' FROM sys.views WHERE name = 'vMasterPackageExecutionLog' AND schema_id = 1 )
BEGIN
     DROP VIEW dbo.vMasterPackageExecutionLog
END
GO

CREATE VIEW dbo.vMasterPackageExecutionLog
AS
/*
 *
 * vMasterPackageExecutionLog
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Calling
 * Application:  
 *
 * Note(s):      
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 12/05/2011, 1.00.0000
 *
 */
SELECT i.SrvrInstncFullName,
       i.SrvrInstncId, 
       SrvrDatabaseName         = d.DatabaseName,
       PackageExecutionId       = l.MiscBigInt1,
       PackageExecutionDt       = l.LogDt,
       PackageName              = l.LogByDesc,
       PackageGUID              = l.MiscGUID1,
       ExecutionGUID            = l.MiscGUID4,
       PackageMajorVersion      = l.MiscInt1,
       PackageMinorVersion      = l.MiscInt2,
       PackageBuildVersion      = l.MiscInt3,
       MachineName              = l.MiscVC1,
       UserName                 = l.MiscVC2,
       ContainerStartTime       = l.MiscDt1,
       PackageStartTime         = l.MiscDt2,
       PackageEndTime           = l.MiscDt3,
       ParentPackageExecutionId = l.MiscBigInt2,
       PackageStateId           = MiscTinyInt1,
       PackageStateDesc         = CONVERT( varchar(10), CASE WHEN MiscTinyint1 = 1
                                                             THEN 'Start'
                                                             WHEN MiscTinyint1 = 128
                                                             THEN 'Info'
                                                             WHEN MiscTinyint1 = 255
                                                             THEN 'End'
                                                             ELSE 'Unknown'
                                                        END )
FROM   dbo.MasterEventLog l INNER JOIN dbo.SrvrInstncDatabase d
          ON l.SrvrInstncDatabaseId = d.SrvrInstncDatabaseId
       INNER JOIN dbo.SrvrInstnc i
          ON d.SrvrInstncId = i.SrvrInstncId 
WHERE  l.LogByApp          = 'Integration Services'
AND    l.LogByAppSubSystem = 'InsPackageExecutionLog'
GO
